local life $life

local ave_panels $ave_panels

local r $r

local deprate $deprate

local KW_per_panel=$KW_per_panel
* https://news.energysage.com/how-many-solar-panels-do-i-need/


clear

import delimited using `"../Data/electricity-generation/output/tract-nerc.csv"'

egen nerc_code=group(nerc)

rename nerc_adj nerc_name

drop v1

save  ../Data/CleanedData/tractNercXXwalk, replace 

sum tract

clear

import delimited using `"../Data/deep-solar/deepsolar_tract.csv"'

clonevar tract=fips

drop state

save  ../Data/CleanedData/DeepSolarStata, replace 




clear

import delimited using `"../Data/google-project-sunroof/project-sunroof-census_tract.csv"'

gen state=floor(region_name/1000000000)

drop if state>56 | state==2 | state ==15 | state==11 // alaska hawaii dc PR



gen state_fips=state

clonevar tract=region_name

sum tract



gen A_t_flow=yearly_sunlight_kwh_total/number_of_panels_total

replace A_t_flow=yearly_sunlight_kwh_median/number_of_panels_median if A_t_flow==.

drop if A_t_flow==.

drop if number_of_panels_total==.


gen PV=0 // present value

forvalues i=0/`life' {

	replace PV=PV+`deprate'^`i'*1/(`r')^`i'
	
}

gen PV_hh=0 // present value

forvalues i=0/`life' {

	replace PV_hh=PV_hh+`deprate'^`i'*1/(1.15)^`i'
	
}
sum PV

sum PV_hh





gen A_t=A_t_flow*PV



*** merge with deepsolar

bysort tract: gen count=_N

drop if count==2 // this is 10 obs

merge 1:1 tract using ../Data/CleanedData/DeepSolarStata



sum total_panel_area_residential solar_system_count_residential

tab _merge

keep if _merge==3

sum total_panel_area_residential solar_system_count_residential




drop _merge

// both from deepsolar


gen M_t=total_panel_area_residential/(1.65*0.992) //*number_of_panels_median

gen BI_t =solar_system_count_residential

replace install_size_kw_buckets="[[-2.5,1]]" if install_size_kw_buckets=="" // empities are coded as blanks

gen CollPerc_t=  education_bachelor_rate + education_master_rate + education_professional_school_r + education_doctoral_r


gen Pol_t=voting_2016_dem_percentage

gen Owner_t=occupancy_owner_rate

sum avg_electricity_retail electricity_price_residential



// demean these variables
foreach var in Pol_t CollPerc_t Owner_t {
	sum `var'
	replace `var'=`var'-r(mean)
}


sum CollPerc_t Pol_t

drop if CollPerc_t==.  | Pol_t==. | Owner_t==.

merge 1:1 tract using ../Data/CleanedData/tractNercXXwalk

bysort state_name: egen modenerc=mode(nerc_code)

replace nerc_code=modenerc if _merge==1

drop if _merge==2

drop _merge


sort tract

merge 1:1 region_name using  ../Data/CleanedData/TractDistancesXXwalk

drop if _merge==2

replace distance=9999999 if _m!=3

sum border

replace border=r(max)+1 if _m!=3

sum border
drop _merge

gen Inc_t=average_household_income

bysort county: egen county_inc=mean(average_household_income)

replace Inc_t=county_inc if Inc_t==.

corr Inc_t CollPerc_t

gen statefips=state_fips


merge m:1 statefips  using suppdata/stateRates

keep if _merge==3

drop _merge

drop state 

merge m:1 state_fips  using ../Data/CleanedData/StatePrices

sort tract






foreach var in tract A_t  M_t BI_t CollPerc_t Pol_t Owner_t nerc_code  border distance population  Inc_t {

	outsheet `var' using ../Data/CleanedData/`var'.csv, replace nonames
}


foreach var in  install_size_kw_buckets {

	export delimited `var' using ../Data/CleanedData/`var'.csv, replace 
}

gen IncTax= stateTax 
gen SalesTax=avgsalestaxratestatelocal
gen PropTax=propertytaxrateavg







foreach var in IncTax SalesTax PropTax {

	outsheet `var' using ../Data/CleanedData/`var'.csv, replace nonames
}


save ../Data/CleanedData/tractData, replace

** make tract to county index crosswalk

gen tract_i=_n


* Step 1: Convert the numeric tract code to a string with leading zeros
gen str11 str_tractcode = string(region_name, "%011.0f")

* Step 2: Extract the first five characters to get the county code
gen county_code = substr(str_tractcode, 1, 5)

destring county_code, replace



keep tract_i county_code

egen county_i=group(county_code)

sum county_code

foreach var in county_i {

	outsheet `var' using ../Data/CleanedData/`var'.csv, replace nonames
}


bysort county_i: gen num=_n

keep if num==1

keep county_i county_code

save ../Data/CleanedData/county_i_xxwalk, replace


